/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * CobaUpload.java
 *
 * Created on Aug 1, 2013, 9:14:05 AM
 */
package View;

import java.io.File;
import javax.swing.JFileChooser;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import javax.swing.filechooser.FileNameExtensionFilter;

/**
 *
 * @author a9uszT
 */
public class CobaUpload extends javax.swing.JFrame {

    /** Creates new form CobaUpload */
    public CobaUpload() {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel1 = new javax.swing.JPanel();
        jTextField1 = new javax.swing.JTextField();
        jButton1 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jButton1.setText("jButton1");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jButton2.setText("Upload");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGap(93, 93, 93)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jButton2)
                    .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 126, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addComponent(jButton1)
                .addContainerGap(90, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGap(103, 103, 103)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton1))
                .addGap(55, 55, 55)
                .addComponent(jButton2)
                .addContainerGap(96, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        JFileChooser chooser = new JFileChooser();
        chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
        chooser.addChoosableFileFilter(new FileNameExtensionFilter("Excel Documents", "xlsx", "xls"));
        int result = chooser.showOpenDialog(this);
        if (result == JFileChooser.APPROVE_OPTION) {
            File selectedFile = chooser.getSelectedFile();
            jTextField1.setText(selectedFile.getAbsolutePath());
        }

    }//GEN-LAST:event_jButton1ActionPerformed

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/si-Pensiun-kp", "root", "");
            con.setAutoCommit(false);
            PreparedStatement pstm = null;
            FileInputStream input = new FileInputStream(jTextField1.getText());
            POIFSFileSystem fs = new POIFSFileSystem(input);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                String id = row.getCell(0).getStringCellValue();
                if (id == null) {
                    id = "-";
                }
                String tempat_surat = row.getCell(1).getStringCellValue();
                if (tempat_surat == null) {
                    tempat_surat = "-";
                }
                String tanggal_surat = row.getCell(2).getStringCellValue();
                String gelar_depan = row.getCell(3).getStringCellValue();
                String nama = row.getCell(4).getStringCellValue();
                String gelar_belakang = row.getCell(5).getStringCellValue();
                String namaFull = gelar_depan + " " + nama + " " + gelar_belakang;
                if (namaFull == null) {
                    namaFull = "-";
                }
                String tempat_lahir = row.getCell(6).getStringCellValue();
                int tanggal_lahir = (int) row.getCell(7).getNumericCellValue();
                int bulan_lahirr = (int) row.getCell(8).getNumericCellValue();
                int tahun_lahir = (int) row.getCell(9).getNumericCellValue();
                String tglLhr = tahun_lahir + "-" + bulan_lahirr + "-" + tanggal_lahir;
                if (tglLhr == null) {
                    tglLhr = "";
                }
                String profesi = row.getCell(10).getStringCellValue();
                if (profesi == null) {
                    profesi = "-";
                }
                String sekolah = row.getCell(11).getStringCellValue();
                int tgl_tmt = (int) row.getCell(12).getNumericCellValue();
                int bln_tmt = (int) row.getCell(13).getNumericCellValue();
                int thn_tmt = (int) row.getCell(14).getNumericCellValue();
                String tmt_full = thn_tmt + "-" + bln_tmt + "-" + tgl_tmt;
                if (tmt_full == null) {
                    tmt_full = "";
                }
                String ms_kerja_tahun = row.getCell(15).getStringCellValue();
                if (ms_kerja_tahun == null) {
                    ms_kerja_tahun = "0";
                }
                String ms_kerja_bulan = row.getCell(16).getStringCellValue();
                if (ms_kerja_bulan == null) {
                    ms_kerja_bulan = "0";
                }
                String gol = row.getCell(17).getStringCellValue();
                if (gol == null) {
                    gol = "-";
                }
                int nip_lama = (int)row.getCell(18).getNumericCellValue();
                String nip_br = row.getCell(19).getStringCellValue();
                String nipbr=nip_br.replaceAll(" ", "");
                if (nip_br == null) {
                    nip_br = "-";
                }
                String berijasah = row.getCell(20).getStringCellValue();
                if (berijasah == null) {
                    berijasah = "-";
                }
                String sql = "INSERT INTO sk_cpns VALUES('" + id + "','" + tempat_surat + "','" + "0000-00-00" + "'"
                        + ",'" + namaFull + "','" + tempat_lahir + "','" + tglLhr + "','" + profesi + "'"
                        + ",'" + sekolah + "','" + tmt_full + "','" + nip_lama + "','" + nipbr + "'"
                        + ",'" + ms_kerja_tahun + "','" + ms_kerja_bulan + "','" + gol + "','" + berijasah + "',current_date,'" + "P" + "')";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
//                System.out.println("id"+id);
//                System.out.println("nama"+namaFull);
//                System.out.println("id"+tempat_lahir);
//                System.out.println("id"+tglLhr);
//                System.out.println("id"+profesi);
//                System.out.println("id"+sekolah);
//                System.out.println("id"+tmt_full);
//                System.out.println("id"+nip_lama);
//                System.out.println("id"+nipbr);
//                System.out.println("id"+ms_kerja_tahun);
//                System.out.println("id"+ms_kerja_bulan);
//                System.out.println("id"+gol);
//                System.out.println("id"+berijasah);
//                System.out.println("Import rows " + i);
            }
            con.commit();
            pstm.close();
            con.close();
            input.close();
            System.out.println("Success import excel to mysql table");
        } catch (ClassNotFoundException e) {
            System.out.println(e);
        } catch (SQLException ex) {
            System.out.println(ex);
        } catch (IOException ioe) {
            System.out.println(ioe);
        }
        // TODO add your handling code here:
    }//GEN-LAST:event_jButton2ActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new CobaUpload().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JTextField jTextField1;
    // End of variables declaration//GEN-END:variables
}
